﻿namespace LogicStudio.Framework.Utilidades
{
    internal class ContenedorScript
    {
        public const string Excepciones =
            @"
SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Category]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Category](
	[CategoryID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryName] [nvarchar](64) NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CategoryLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CategoryLog](
	[CategoryLogID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryID] [int] NOT NULL,
	[LogID] [int] NOT NULL,
 CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED 
(
	[CategoryLogID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Log]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Log](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[EventID] [int] NULL,
	[Priority] [int] NOT NULL,
	[Severity] [nvarchar](32) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[Timestamp] [varchar](30) NOT NULL,
	[MachineName] [nvarchar](32) NOT NULL,
	[AppDomainName] [nvarchar](512) NOT NULL,
	[ProcessID] [nvarchar](256) NOT NULL,
	[ProcessName] [nvarchar](512) NOT NULL,
	[ThreadName] [nvarchar](512) NULL,
	[Win32ThreadId] [nvarchar](128) NULL,
	[Message] [nvarchar](1500) NULL,
	[FormattedMessage] [ntext] NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[InsertCategoryLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE InsertCategoryLog
	@categoryID INT,
	@logId INT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @CatLogID INT
	SELECT @CatLogID FROM CategoryLog WHERE CategoryID=@categoryID and LogID = @logId
	IF @CatLogID IS NULL
	BEGIN
		INSERT INTO CategoryLog (CategoryID, LogID) VALUES(@categoryID, @logId)
		RETURN @@IDENTITY
	END
	ELSE RETURN @CatLogID
END' 
END

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[AddCategory]
	-- Add the parameters for the function here
	@categoryName nvarchar(64),
	@logID int
AS
BEGIN
	SET NOCOUNT ON;
    DECLARE @CatID INT
	SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @categoryName
	IF @CatID IS NULL
	BEGIN
		INSERT INTO Category (CategoryName) VALUES(@categoryName)
		SELECT @CatID = @@IDENTITY
	END

	EXEC InsertCategoryLog @CatID, @logID 

	RETURN @CatID
END' 
END

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ClearLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE ClearLogs
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM CategoryLog
	DELETE FROM [Log]
    DELETE FROM Category
END' 
END

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[WriteLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
/****** Object:  Stored Procedure dbo.WriteLog    Script Date: 10/1/2004 3:16:36 PM ******/

CREATE PROCEDURE [dbo].[WriteLog]
(
	@eventID int, 
	@priority int, 
	@severity nvarchar(32), 
	@title nvarchar(256), 
	@timestamp varchar(30),
	@machineName nvarchar(32), 
	@AppDomainName nvarchar(512),
	@ProcessID nvarchar(256),
	@ProcessName nvarchar(512),
	@ThreadName nvarchar(512),
	@Win32ThreadId nvarchar(128),
	@message nvarchar(1500),
	@formattedmessage ntext,
	@LogId int OUTPUT
)
AS 

	INSERT INTO [Log] (
		EventID,
		Priority,
		Severity,
		Title,
		[Timestamp],
		MachineName,
		AppDomainName,
		ProcessID,
		ProcessName,
		ThreadName,
		Win32ThreadId,
		Message,
		FormattedMessage
	)
	VALUES (
		@eventID, 
		@priority, 
		@severity, 
		@title, 
		@timestamp,
		@machineName, 
		@AppDomainName,
		@ProcessID,
		@ProcessName,
		@ThreadName,
		@Win32ThreadId,
		@message,
		@formattedmessage)

	SET @LogId = @@IDENTITY
	RETURN @LogId' 
END

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Category') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY(	[CategoryID])
REFERENCES [dbo].[Category] (	[CategoryID]);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Log') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY(	[LogID])
REFERENCES [dbo].[Log] (	[LogID]);

SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF 

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CategoryLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
	DECLARE @bErrors as bit

	BEGIN TRANSACTION
	SET @bErrors = 0

	CREATE NONCLUSTERED INDEX [ixCategoryLog] ON [dbo].[CategoryLog] ([LogID] ASC, [CategoryID] ASC )
	IF( @@error <> 0 ) SET @bErrors = 1

	IF( @bErrors = 0 )
	  COMMIT TRANSACTION
	ELSE
	  ROLLBACK TRANSACTION
END";

        public const string Auditoria =
            @"IF OBJECT_ID(N'[dbo].[Auditoria]', 'U') IS NULL
    CREATE TABLE [dbo].[Auditoria](
    [AuditoriaId] uniqueidentifier NOT NULL,
    [Usuario] varchar(32)  NOT NULL,
    [FechaHora] datetime  NOT NULL,
    [Accion] varchar(50)  NULL,
    [Descripcion] varchar(250)  NULL,
    [DireccionIP] varchar(16)  NULL,
    [MensajeError] varchar(max)  NULL,    
    CONSTRAINT [PK_Auditoria]
    PRIMARY KEY CLUSTERED ([AuditoriaId] ASC)
);
 IF OBJECT_ID(N'[dbo].[AuditoriaDetalle]', 'U') IS NULL
    CREATE TABLE [dbo].[AuditoriaDetalle] (
    [AuditoriaId] uniqueidentifier  NOT NULL,
    [Tabla] varchar(30)  NOT NULL,
    [Usuario] varchar(32)  NOT NULL,
    [Accion] varchar(50)  NOT NULL,
    [DatosNuevos] varchar(500) NULL,
	[DatosAnteriores] varchar(500) NULL,
	CONSTRAINT [PK_AuditoriaDetalle] PRIMARY KEY CLUSTERED ([AuditoriaId], [Tabla] ASC),
	CONSTRAINT [FK_AuditoriaDetalle_Auditoria] FOREIGN KEY ([AuditoriaId])
    REFERENCES [dbo].[Auditoria]([AuditoriaId]) ON DELETE NO ACTION ON UPDATE NO ACTION);";
    }
}